package org.homeorganiser.flex.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import javax.sql.DataSource;

import org.homeorganiser.flex.dto.User;
import org.homeorganiser.flex.util.Constant;


public class UserDAO implements IUserDAO {

	private final DataSource dataSource;
	
    
	
	public UserDAO(DataSource dataSource) {
	        this.dataSource = dataSource;
	 }
	
	public List<User> findAll(int type) {
		// TODO Auto-generated method stub
		return null;
	}

	public List<User> findByName(String name) {
		// TODO Auto-generated method stub
		return null;
	}

	public User findById(int id) {
		// TODO Auto-generated method stub
		return null;
	}

	public User create(User usr) {
		 Connection c = null;
	     PreparedStatement ps = null;
	        try {
	            c = this.dataSource.getConnection();
	            ps = c.prepareStatement(Constant.SQL_INSERT_USER,
	                new String[] { "ID" });
	           
	            ps.setString(1, usr.getUserId());
	            ps.setString(2, usr.getPassword());
	            ps.setString(3, usr.getFirstName());
	            ps.setString(4, usr.getLastName());
	            ps.setString(5, usr.getAddress());
	            ps.setString(6, usr.getCity());
	            ps.setString(7, usr.getState());
	            ps.setString(8, usr.getZip());
	            ps.setString(9, usr.getPhone());
	            ps.setString(10, usr.getEmail());
	            ps.setString(11, usr.getSecurityQuestion());
	            ps.setString(12, usr.getSecurityAnswer());
	            
	            ps.executeUpdate();
	            ResultSet rs = ps.getGeneratedKeys();
	            rs.next();
	            // Update the id in the returned object. This is important as this value must be returned to the client.
	            int id = rs.getInt(1);
	            usr.setId(id);
	        } catch (Exception e) {
	            e.printStackTrace();
	            throw new RuntimeException(e);
	        }
	        return usr;
	}

	public boolean update(User usr) {
		Connection c = null;
        try {
            c = this.dataSource.getConnection();
            PreparedStatement ps = c.prepareStatement(Constant.SQL_UPDATE_USER_DETAIL);
            ps.setString(1, usr.getUserId());
            ps.setString(2, usr.getPassword());
            ps.setString(3, usr.getPhone());
            ps.setString(4, usr.getEmail());
            ps.setInt(5, usr.getId());
            
            return ps.executeUpdate() == 1;
        } catch (SQLException e) {
            e.printStackTrace();
            throw new RuntimeException(e);
        }
	}

	public boolean remove(User item) {
		// TODO Auto-generated method stub
		return false;
	}

	public String validateUser(String userName, String password) {
		
		Connection conn = null;
		ResultSet rs;
		PreparedStatement ps;
		String usrpassword;
        try {
        	conn = this.dataSource.getConnection();
            ps = conn.prepareStatement(Constant.SQL_VALIDATE_USER);
            ps.setString(1, userName );
            rs = ps.executeQuery();
//            if(!rs.next()){
//            	return Constant.MESSAGE_INVALID_USERID;
//            }
            if (rs.next()) {
               usrpassword = rs.getString("password");	
               if(usrpassword.equals(password)){
            	   return Constant.MESSAGE_VALID_LOGIN;
               }else{
            	   return Constant.MESSAGE_INVALID_LOGIN;
               }
            }else{
            	return Constant.MESSAGE_INVALID_USERID;
            }
        } catch (SQLException e) {
            e.printStackTrace();
            throw new RuntimeException(e);
        }
	}
   //For Checking UserName if it is avaliable
	public Boolean checkUserName(String userName) {
		Connection conn = null;
		ResultSet rs;
		PreparedStatement ps;
		try {
        	conn = this.dataSource.getConnection();
            ps = conn.prepareStatement(Constant.SQL_CHECK_USER_NAME);
            ps.setString(1, userName );
            rs = ps.executeQuery();
            if(rs.next()){
            	return false;
            }else{
            	return true;
            }
        } catch (SQLException e) {
            e.printStackTrace();
            throw new RuntimeException(e);
        }
	}

	public String getEmail(String userName) {
		Connection conn = null;
		ResultSet rs;
		PreparedStatement ps;
		String usrEmail = null;
		try {
        	conn = this.dataSource.getConnection();
            ps = conn.prepareStatement(Constant.SQL_GET_EMAIL_ID);
            ps.setString(1, userName );
            rs = ps.executeQuery();
            
            while(rs.next()){
            	usrEmail = rs.getString("email");
            }
        } catch (SQLException e) {
            e.printStackTrace();
            throw new RuntimeException(e);
        }
		
		return usrEmail;
	}

	public String getPassword(String userName) {
		Connection conn = null;
		ResultSet rs;
		PreparedStatement ps;
		String usrPassword = null;
		try {
        	conn = this.dataSource.getConnection();
            ps = conn.prepareStatement(Constant.SQL_GET_PASSWORD);
            ps.setString(1, userName );
            rs = ps.executeQuery();
            
            while(rs.next()){
            	usrPassword = rs.getString("password");
            }
        } catch (SQLException e) {
            e.printStackTrace();
            throw new RuntimeException(e);
        }
		return usrPassword;
	}

	public List<User>  getUsers(String userName, String firstName,
			String lastName, String usrEmail) {
		Connection conn = null;
		ResultSet rs;
		PreparedStatement ps;
		List<User> usrList = new ArrayList<User>();
		User usr;
		try {
				
			String searchQuery = buildSearchQuery(userName,firstName,lastName,usrEmail);
			
			conn = this.dataSource.getConnection();
            ps = conn.prepareStatement(searchQuery);
            rs = ps.executeQuery();
            
            while(rs.next()){
            	usr = new User();
            	usr.setId(rs.getInt("id"));
            	usr.setUserId(rs.getString("user_id"));
            	usr.setFirstName(rs.getString("first_name"));
            	usr.setLastName(rs.getString("last_name"));
            	usr.setEmail(rs.getString("email"));
            	usrList.add(usr);
            }
        } catch (SQLException e) {
            e.printStackTrace();
            throw new RuntimeException(e);
        }
		return usrList;
	}

	private String buildSearchQuery(String userName,String firstName, String lastName,
			String usrEmail) {
		
		String searchQuery = Constant.SQL_GET_USERS;
		boolean isExists = false;
		if((userName != null || firstName != null || lastName !=null || usrEmail!= null) &&
				(userName.trim() != "" || firstName.trim() != "" || lastName.trim() !="" || usrEmail.trim() != "")){
			searchQuery += " Where ";  
		}
		if(userName != null && userName.trim() != ""){
			searchQuery += " user_id = '"+userName+"'";
			isExists = true;
		}
		if(firstName != null && firstName.trim() != ""){
			if(isExists){
				searchQuery += " and first_name = '"+firstName+"'";
			}else{
				searchQuery += " first_name = '"+firstName+"'";
			}
			isExists = true;
		}
		if(lastName != null && lastName.trim() != ""){
			if(isExists){
				searchQuery += " and last_name = '"+lastName+"'";
			}else{
				searchQuery += " last_name = '"+lastName+"'";
			}
			isExists = true;
		}
		if(usrEmail != null && usrEmail.trim() != ""){
			if(isExists){
				searchQuery += " and email = '"+usrEmail+"'";
			}else{
				searchQuery += " email = '"+usrEmail+"'";
			}
		}
		return searchQuery;
	}

	public User getUsersDetail(int userReg) {
		Connection conn = null;
		ResultSet rs;
		PreparedStatement ps;
		User usr = null;
		try {
        	conn = this.dataSource.getConnection();
            ps = conn.prepareStatement(Constant.SQL_GET_USER_DETAIL);
            ps.setInt(1, userReg );
            rs = ps.executeQuery();
            while(rs.next()){
            	usr = new User();
            	usr.setId(rs.getInt("id"));
            	usr.setUserId(rs.getString("user_id"));
            	usr.setPassword(rs.getString("password"));
            	usr.setFirstName(rs.getString("first_name"));
            	usr.setLastName(rs.getString("last_name"));
            	usr.setPhone(rs.getString("phone"));
            	usr.setEmail(rs.getString("email"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
            throw new RuntimeException(e);
        }
		return usr;
	}

	public boolean removeAll(int type) {
		// TODO Auto-generated method stub
		return false;
	}
    	
}
